﻿<%@ WebHandler Language="C#" Class="ZSSQCPYearData" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Web.SessionState;
using System.IO;
using ReportBusiness;

public class ZSSQCPYearData : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public void ProcessRequest(HttpContext context)
    {
        //本年各月统计
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetMonthData")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            //指标项名称
            if (context.Request.Form["itemname"] != null)
            {
                para["itemname"] = context.Request.Form["itemname"].ToString();
            }
            //数据来源
            if (context.Request.Form["datafrom"] != null)
            {
                para["datafrom"] = context.Request.Form["datafrom"].ToString();
            }

            string sql = "";

            if (para["datafrom"].ToString() == "指标维护")
            {
                sql = @"
SELECT t.planmonth || '月' planmonth,SUM(t.planvalue) planvalue,SUM(t.actualvalue) actualvalue,SUM(t.notfinish) notfinish
FROM 
(
SELECT to_char(wmd.completedate,'mm') planmonth,REPLACE(w.workcentername,'装试','') workcentername,
       DECODE(fm.sqcdptype,0,'S',1,'Q',2,'C',3,'D',4,'P') sqcdptype,
       wm.itemname,wm.planvalue,nvl(wmd.actualvalue,0) actualvalue,
       wm.planvalue - nvl(wmd.actualvalue,0) notfinish,
       decode(wmd.status,0,'未录入',1,'正常',2,'预警') status
FROM sqcdp_workcenter_maint_details wmd
LEFT JOIN sqcdp_workcenter_maint wm ON wm.sqcdp_workcenter_maintid = wmd.sqcdp_workcenter_maintid
LEFT JOIN sqcdp_factory_maint fm ON fm.sqcdp_factory_maintid = wm.sqcdp_factory_maintid
LEFT JOIN factory f ON f.factoryid = fm.factoryid
LEFT JOIN Workcenter w ON w.workcenterid = wm.workcenterid
WHERE w.workcentername IS NOT NULL AND f.factoryname='发动机总体事业部'
      AND TRUNC(wmd.completedate) BETWEEN TRUNC(SYSDATE,'yyyy') AND (add_months(TRUNC(SYSDATE,'yyyy'),12)-1)
";
                sql += string.Format(" AND wm.itemname = '{0}' ", para["itemname"].ToString());
                sql += @"
) t
GROUP BY t.planmonth
ORDER BY t.planmonth";
            }
            else
            {
                sql = @"
SELECT t.planmonth || '月' planmonth,COUNT(t.operationcontent) planvalue,
       NVL(SUM(CASE WHEN t.status = '正常' THEN 1 ELSE 0 END),0) actualvalue,
       COUNT(t.operationcontent) - NVL(SUM(CASE WHEN t.status = '正常' THEN 1 ELSE 0 END),0) notfinish
FROM
(
SELECT to_char(mfd.completedate,'mm') planmonth,REPLACE(w.workcentername,'装试','') workcentername,
       DECODE(mf.sqcdptype,0,'S',1,'Q',2,'C',3,'D',4,'P') sqcdptype,
       mf.operationcontent,
       DECODE(mfd.status,0,'未录入',1,'正常',2,'预警') status
FROM manageroperationflowcomplete mfd
LEFT JOIN manageroperationflow mf ON mf.manageroperationflowid = mfd.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = mfd.createemployeeid--mf.operateemployee
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN workcenter w ON w.workcenterid = sv.workcenterid
WHERE w.workcentername IS NOT NULL AND f.factoryname='发动机总体事业部' 
      AND TRUNC(mfd.completedate) BETWEEN TRUNC(SYSDATE,'yyyy') AND (add_months(TRUNC(SYSDATE,'yyyy'),12)-1)
";
                sql += string.Format(" AND mf.operationcontent = '{0}' ", para["itemname"].ToString());
                sql += @"
) t
GROUP BY t.planmonth
ORDER BY t.planmonth";
            }

            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //本年各工区统计
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetWorkcenterData")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            //指标项名称
            if (context.Request.Form["itemname"] != null)
            {
                para["itemname"] = context.Request.Form["itemname"].ToString();
            }
            //数据来源
            if (context.Request.Form["datafrom"] != null)
            {
                para["datafrom"] = context.Request.Form["datafrom"].ToString();
            }

            string sql = "";

            if (para["datafrom"].ToString() == "指标维护")
            {
                sql = @"
SELECT t.workcentername,SUM(t.planvalue) planvalue,SUM(t.actualvalue) actualvalue,SUM(t.notfinish) notfinish
FROM 
(
SELECT REPLACE(w.workcentername,'装试','') workcentername,
       DECODE(fm.sqcdptype,0,'S',1,'Q',2,'C',3,'D',4,'P') sqcdptype,
       wm.itemname,wm.planvalue,nvl(wmd.actualvalue,0) actualvalue,
       wm.planvalue - nvl(wmd.actualvalue,0) notfinish,
       decode(wmd.status,0,'未录入',1,'正常',2,'预警') status
FROM sqcdp_workcenter_maint_details wmd
LEFT JOIN sqcdp_workcenter_maint wm ON wm.sqcdp_workcenter_maintid = wmd.sqcdp_workcenter_maintid
LEFT JOIN sqcdp_factory_maint fm ON fm.sqcdp_factory_maintid = wm.sqcdp_factory_maintid
LEFT JOIN factory f ON f.factoryid = fm.factoryid
LEFT JOIN Workcenter w ON w.workcenterid = wm.workcenterid
WHERE w.workcentername IS NOT NULL AND f.factoryname='发动机总体事业部'
      AND TRUNC(wmd.completedate) BETWEEN TRUNC(SYSDATE,'yyyy') AND (add_months(TRUNC(SYSDATE,'yyyy'),12)-1)
";
                sql += string.Format(" AND wm.itemname = '{0}' ", para["itemname"].ToString());
                sql += @"
) t
GROUP BY t.workcentername
ORDER BY t.workcentername";

            }
            else
            {
                sql = @"
SELECT t.workcentername,COUNT(t.operationcontent) planvalue,
       NVL(SUM(CASE WHEN t.status = '正常' THEN 1 ELSE 0 END),0) actualvalue,
       COUNT(t.operationcontent) - NVL(SUM(CASE WHEN t.status = '正常' THEN 1 ELSE 0 END),0) notfinish
FROM
(
SELECT REPLACE(w.workcentername,'装试','') workcentername,
       DECODE(mf.sqcdptype,0,'S',1,'Q',2,'C',3,'D',4,'P') sqcdptype,
       mf.operationcontent,
       DECODE(mfd.status,0,'未录入',1,'正常',2,'预警') status
FROM manageroperationflowcomplete mfd
LEFT JOIN manageroperationflow mf ON mf.manageroperationflowid = mfd.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = mfd.createemployeeid--mf.operateemployee
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN workcenter w ON w.workcenterid = sv.workcenterid
WHERE w.workcentername IS NOT NULL AND f.factoryname='发动机总体事业部' 
      AND TRUNC(mfd.completedate) BETWEEN TRUNC(SYSDATE,'yyyy') AND (add_months(TRUNC(SYSDATE,'yyyy'),12)-1)
";
                sql += string.Format(" AND mf.operationcontent = '{0}' ", para["itemname"].ToString());
                sql += @"
) t
GROUP BY t.workcentername
ORDER BY t.workcentername";
            }
            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
    }
}